//
//  LYCityDataDBTool.m
//  artist
//
//  Created by Jay_Apple on 16/4/18.
//  Copyright © 2016年 viviyoo. All rights reserved.
//

#import "LYCityDataDBTool.h"
#import "LYCityDataModel.h"

static NSString *const tableName = @"t_city";
static NSString *const openCityTableName = @"t_open_city";

@implementation LYCityDataDBTool

+(void)initialize{
    
        
    [VVYSaveDataManager createTableWithName:tableName andKeyTypes:@{
                                                                    @"i" : @"integer NOT NULL UNIQUE",
                                                                    @"p" : @"integer NOT NULL",
                                                                    @"n" : @"text NOT NULL",
                                                                    }];
    [VVYSaveDataManager createTableWithName:openCityTableName andKeyTypes:@{
                                                                    @"i" : @"integer NOT NULL UNIQUE",
                                                                    @"p" : @"integer NOT NULL",
                                                                    @"n" : @"text NOT NULL",
                                                                    @"l" : @"integer NOT NULL"
                                                                    }];

}

/**
 *  储存城市数据
 */
+ (void)saveCityData:(NSArray *)citydata withType:(cityDataType)type{
    
    FMDatabase *db = [VVYSaveDataManager getDB];
    
    NSString *table = nil;
    
    switch (type) {
        case cityDataTypeNormal:{
            table = tableName;
            NSString *SQL = [NSString stringWithFormat:@"REPLACE INTO %@ (i, p, n) VALUES (?, ?, ?);", table];
            
            for (NSDictionary *dict in citydata) {
                [db executeUpdate:SQL, dict[@"i"], dict[@"p"], dict[@"n"]];
            }
    }

            break;
        case cityDataTypeOpen:{
            table = openCityTableName;
            NSString *SQL = [NSString stringWithFormat:@"REPLACE INTO %@ (i, p, n, l) VALUES (?, ?, ?, ?);", table];
            
            for (NSDictionary *dict in citydata) {
                [db executeUpdate:SQL, dict[@"i"], dict[@"p"], dict[@"n"], dict[@"l"]];
            }
        }
            break;

    }
    
  
    
    
}





/**
 *  获取开通城市数据
 */
+ (NSArray <LYCityDataModel *>*)getOpenCityData{

    FMDatabase *db = [VVYSaveDataManager getDB];
    NSMutableArray *array = [NSMutableArray array];
    
    //拿出城市
    FMResultSet *cityResult = [db executeQuery:@"SELECT * FROM t_open_city WHERE l = 2"];
    while ([cityResult next]) {
        LYCityDataModel *model = [[LYCityDataModel alloc] init];
        model.cityName = [cityResult stringForColumn:@"n"];
        
        int cityID = [cityResult intForColumn:@"i"];
        //通过城市查区域
        FMResultSet *areaResult = [db executeQueryWithFormat:@"SELECT * FROM t_open_city WHERE p = %d",cityID];
        
        NSMutableArray *areaArray = [NSMutableArray array];
        
        while ([areaResult next]) {
            
            NSString *areaName = [areaResult stringForColumn:@"n"];
            
            [areaArray addObject:areaName];
            
        }
        
        model.areas = areaArray;
        
        [array addObject:model];
        
    }
    
    return array;
}

/**
 *  获取地区id
 */
+ (int)getIDWithName:(NSString *)name{

    FMDatabase *db = [VVYSaveDataManager getDB];
    
    FMResultSet *result = [db executeQueryWithFormat:@"SELECT i FROM t_city WHERE n = %@", name];
    
    while ([result next]) {
        
        return [result intForColumnIndex:0];
    }
    
    return -1;
}

/**
 *  根据名字查子地区
 */
+ (NSArray <NSString *>*)getSubAreaWithName:(NSString *)name{

    //通过名字查询出id
    int ID = [self getIDWithName:name];
    
    //通过id查询子地区
    FMDatabase *db = [VVYSaveDataManager getDB];
    
    FMResultSet *result = [db executeQueryWithFormat:@"SELECT n FROM t_city WHERE p = %d",ID];
    
    NSMutableArray *array = [NSMutableArray array];
    
    while ([result next]) {
        
        NSString *name = [result stringForColumnIndex:0];
        
        [array addObject:name];
    }
    
    return [array copy];

}

/**
 *  取出省份
 */
+ (NSArray <NSString *>*)getProvinces{

    NSMutableArray *array = [NSMutableArray array];
    
    FMDatabase *db = [VVYSaveDataManager getDB];
    
    FMResultSet *result = [db executeQuery:@"SELECT n FROM t_city WHERE p = 0"];
    
    while ([result next]) {
        
        NSString *provinceName = [result stringForColumnIndex:0];
        [array addObject:provinceName];
        
    }
    
    return [array copy];
}

/**
 *  取出上级地区id
 */
+ (NSInteger)getSuperCityIDWithAreaName:(NSString *)area{

    //找到上级地区id
    FMDatabase *db = [VVYSaveDataManager getDB];
    
    FMResultSet *result = [db executeQueryWithFormat:@"SELECT p FROM t_city WHERE n = %@",area];
    
    while ([result next]) {
        
        NSInteger superCityID = [result intForColumnIndex:0];
        return superCityID;
    }
    
    return -1;
}


@end
